package com.engine.kq.biz;

import com.alibaba.fastjson.JSON;
import com.engine.kq.entity.KQGroupEntity;
import com.engine.kq.log.KQLog;
import weaver.cache.*;
import weaver.common.DateUtil;
import weaver.conn.RecordSet;
import weaver.general.StaticObj;
import weaver.general.Util;
import weaver.hrm.resource.ResourceComInfo;
import java.io.PrintWriter;
import java.io.Serializable;
import java.io.StringWriter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

public class KQGroupMemberComInfo extends CacheBase implements Serializable {
  /*
   * 需要关注 : 如果完全在 initCache 自己定义数据初始化， 这个字段可以不定义
   */
  protected static String TABLE_NAME = "";

  /*
   * 需要关注 : 如果完全在 initCache 自己定义数据初始化，或者不需要指定固定的条件， 这个字段可以不定义
   * sql中的where信息，不要以where开始
   */
  protected static String TABLE_WHERE = null;

  /*
   * 需要关注 : 如果完全在 initCache 自己定义数据初始化，或者不需要指定顺序， 这个字段可以不定义 sql中的order
   * by信息，不要以order by开始
   *
   */
  protected static String TABLE_ORDER = null;

  @PKColumn(type = CacheColumnType.NUMBER)
  protected static String PK_NAME = "id";

  @CacheColumn
  protected static int group;

  private KQLog kqLog = new KQLog();

  private boolean isFormat = false;
  
  @Override
  protected boolean autoInitIfNotFound() {
     return false;
  }

  private StaticObj staticobj = null;

  @Override
  public CacheItem initCache(String key) {

    if (key == null || "".equals(key.trim())) {
      return null;
    }

    RecordSet rs = new RecordSet();
    String sql = "";
    kqLog = new KQLog();
    ConcurrentHashMap<String, List> resourceKQGroups = new ConcurrentHashMap<>();
    //考勤组优先级
    try {
      ResourceComInfo resourceComInfo = new ResourceComInfo();

      String subcompanyid = Util.null2String(resourceComInfo.getSubCompanyID(key));//分部
      String departmentid = Util.null2String(resourceComInfo.getDepartmentID(key));//部门
      String jobtitle = Util.null2String(resourceComInfo.getJobTitle(key));//岗位

      //人员
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE a.id=b.typevalue and b.type =1 and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1')" +
              " and a.id = "+key+
              " order by b.groupid desc ";
      rs.executeQuery(sql);
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }    

      //部门
      if(departmentid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b , kq_group c" +
                " WHERE a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3 " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.departmentid = " + departmentid +
                " and a.id="+key+
                " order by b.groupid desc";
        rs.executeQuery(sql);
        while (rs.next()) {
          setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
        }
      }
	  
	  //分部
      if(subcompanyid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
                " WHERE a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2 " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.subcompanyid1 = " + subcompanyid +
                " and a.id="+key+
                " order by b.groupid desc ";
        rs.executeQuery(sql);
        while (rs.next()) {
          setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
        }
      }

      //岗位
      if(jobtitle.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
                " WHERE a.jobtitle = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=5 " +
                " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.jobtitle = " + jobtitle +
                " and a.id="+key+
                " order by b.groupid desc ";
        rs.executeQuery(sql);
        while (rs.next()) {
          setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
        }
      }

      //所有人
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE (b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto) and b.groupid=c.id " +
              " and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
              " and a.id = "+key+
              " order by b.groupid desc ";
      rs.executeQuery(sql);
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }

      if(resourceKQGroups.size()>0){
        CacheItem cacheItem = createCacheItem();
        Iterator<Map.Entry<String, List>> iterator = resourceKQGroups.entrySet().iterator();
        while (iterator.hasNext()) {
          Map.Entry<String, List> entry = iterator.next();
          String id = entry.getKey();
          List value = entry.getValue();
          cacheItem.set(PK_INDEX, id);
          cacheItem.set(group, value);
          modifyCacheItem(key, cacheItem);
        }
        return cacheItem;
      }
    } catch (Exception e) {
      kqLog.info(e);
    }
    return null;
  }

  private void getHasValidateGroupInfo() {
    RecordSet rs = new RecordSet();
    String backFields = " b.groupid, b.typevalue, b.validatefrom as validatefrom, b.validateto as validateto,b.seclevel as groupseclevel,b.seclevelto as groupseclevelto, b.jobtitlelevel as jobtitlelevel, b.jobtitlelevelvalue as jobtitlelevelvalue ";
    String sqlWhere = " and ((validatefrom <>'"+KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE+"') or (validateto  <>'"+KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE+"'))";
    //人员
    String sql = " SELECT "+backFields+"  FROM HrmResource a, kq_groupmember b, kq_group c " +
            " WHERE a.id=b.typevalue and b.type =1 and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1')" +
              sqlWhere + " order by b.groupid desc ";
    rs.executeQuery(sql);
//    rs.writeLog("sql:::::::::"+sql);
    List<String> userGroupList = null;
    while (rs.next()) {
      String param = "type#1#"+rs.getString("typevalue");
      String groupId = rs.getString("groupid");
      String validatefrom = Util.null2String(rs.getString("validatefrom"), KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE);
      String validateto = Util.null2String(rs.getString("validateto"), KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE);
      String valueParam = groupId+"#"+validatefrom+"#"+validateto;
      if(staticobj.getObject("User&GroupMember") == null) {
        userGroupList = new ArrayList<>();
      } else {
        userGroupList = (List<String>)staticobj.getRecordFromObj("User&GroupMember",param);
        if(userGroupList == null) {
          userGroupList = new ArrayList<>();
        }
      }
      if(!userGroupList.contains(valueParam)) {
        userGroupList.add(valueParam);
        staticobj.putRecordToObj("User&GroupMember",param,userGroupList);
      }
//      rs.writeLog("userGroupList:::::::::"+JSON.toJSONString(userGroupList));
    }
    //部门
    sql = " SELECT "+backFields+" FROM HrmResource a, kq_groupmember b , kq_group c" +
            " WHERE a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3 " +
            " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
            sqlWhere +
            " order by b.groupid desc";
    rs.executeQuery(sql);
    List<String> departGroupList = null;
    while (rs.next()) {
      String param = "type#3#"+rs.getString("typevalue");
      String groupId = rs.getString("groupid");
      String validatefrom = Util.null2String(rs.getString("validatefrom"), KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE);
      String validateto = Util.null2String(rs.getString("validateto"), KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE);
       String groupseclevel = Util.null2String(rs.getString("groupseclevel"));
      String groupseclevelto = Util.null2String(rs.getString("groupseclevelto"));
      String valueParam = groupId+"#"+validatefrom+"#"+validateto+"#"+groupseclevel+"#"+groupseclevelto;
      if(staticobj.getObject("Department&GroupMember") == null) {
        departGroupList = new ArrayList<>();
      } else {
        departGroupList = (List<String>)staticobj.getRecordFromObj("Department&GroupMember",param);
        if(departGroupList == null) {
          departGroupList = new ArrayList<>();
        }
      }
      if(!departGroupList.contains(valueParam)) {
        departGroupList.add(valueParam);
        staticobj.putRecordToObj("Department&GroupMember",param,departGroupList);
      }
    }
    //分部
    sql = " SELECT "+backFields+" FROM HrmResource a, kq_groupmember b, kq_group c " +
            " WHERE a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2 " +
            " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
            sqlWhere +
            " order by b.groupid desc ";
    rs.executeQuery(sql);
    List<String> subCompanyGroupList = null;
    while (rs.next()) {
      String param = "type#2#"+rs.getString("typevalue");
      String groupId = rs.getString("groupid");
      String validatefrom = Util.null2String(rs.getString("validatefrom"), KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE);
      String validateto = Util.null2String(rs.getString("validateto"), KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE);
      String groupseclevel = Util.null2String(rs.getString("groupseclevel"));
      String groupseclevelto = Util.null2String(rs.getString("groupseclevelto"));
      String valueParam = groupId+"#"+validatefrom+"#"+validateto+"#"+groupseclevel+"#"+groupseclevelto;
      if(staticobj.getObject("SubCompany&GroupMember") == null) {
        subCompanyGroupList = new ArrayList<>();
      } else {
        subCompanyGroupList = (List<String>)staticobj.getRecordFromObj("SubCompany&GroupMember",param);
        if(subCompanyGroupList == null) {
          subCompanyGroupList = new ArrayList<>();
        }
      }
      if(!subCompanyGroupList.contains(valueParam)) {
        subCompanyGroupList.add(valueParam);
        staticobj.putRecordToObj("SubCompany&GroupMember",param,subCompanyGroupList);
      }
    }
    //岗位
    sql = " SELECT "+backFields+" FROM HrmResource a, kq_groupmember b, kq_group c " +
            " WHERE a.jobtitle = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=5 " +
            " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
            " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
            sqlWhere +
            " order by b.groupid desc ";
    rs.executeQuery(sql);
    List<String> jobtitleGroupList = null;
    while (rs.next()) {
      String param = "type#5#"+rs.getString("typevalue");
      String groupId = rs.getString("groupid");
      String validatefrom = Util.null2String(rs.getString("validatefrom"), KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE);
      String validateto = Util.null2String(rs.getString("validateto"), KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE);
      String groupseclevel = Util.null2String(rs.getString("groupseclevel"));
      String groupseclevelto = Util.null2String(rs.getString("groupseclevelto"));
      String jobtitlelevel = Util.null2String(rs.getString("jobtitlelevel"));
      jobtitlelevel = "".equals(jobtitlelevel) ? "-1" : jobtitlelevel;
      String jobtitlelevelvalue = Util.null2String(rs.getString("jobtitlelevelvalue"));
      jobtitlelevelvalue = "".equals(jobtitlelevelvalue) ? "-1" : ","+jobtitlelevelvalue+",";
      String valueParam = groupId+"#"+validatefrom+"#"+validateto+"#"+groupseclevel+"#"+groupseclevelto+"#"+jobtitlelevel+"#"+jobtitlelevelvalue;
      if(staticobj.getObject("Jobtitle&GroupMember") == null) {
        jobtitleGroupList = new ArrayList<>();
      } else {
        jobtitleGroupList = (List<String>)staticobj.getRecordFromObj("Jobtitle&GroupMember",param);
        if(jobtitleGroupList == null) {
          jobtitleGroupList = new ArrayList<>();
        }
      }
      if(!jobtitleGroupList.contains(valueParam)) {
        jobtitleGroupList.add(valueParam);
        staticobj.putRecordToObj("Jobtitle&GroupMember",param,jobtitleGroupList);
      }
    }
    //所有人
    sql = " SELECT "+backFields+" FROM HrmResource a, kq_groupmember b, kq_group c " +
            " WHERE (b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto) and b.groupid=c.id " +
            " and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
            sqlWhere +
            " order by b.groupid desc ";
    rs.executeQuery(sql);
    rs.executeQuery(sql);
    List<String> allGroupList = null;
    while (rs.next()) {
      String param = "type#6#"+rs.getString("typevalue");
      String groupId = rs.getString("groupid");
      String validatefrom = Util.null2String(rs.getString("validatefrom"), KQGroupBiz.GROUPMEMBER_VALIDATE_FROMDATE);
      String validateto = Util.null2String(rs.getString("validateto"), KQGroupBiz.GROUPMEMBER_VALIDATE_TODATE);
      String groupseclevel = Util.null2String(rs.getString("groupseclevel"));
      String groupseclevelto = Util.null2String(rs.getString("groupseclevelto"));
      String valueParam = groupId+"#"+validatefrom+"#"+validateto+"#"+groupseclevel+"#"+groupseclevelto;
      if(staticobj.getObject("All&GroupMember") == null) {
        allGroupList = new ArrayList<>();
      } else {
        allGroupList = (List<String>)staticobj.getRecordFromObj("All&GroupMember",param);
        if(allGroupList == null) {
          allGroupList = new ArrayList<>();
        }
      }
      if(!allGroupList.contains(valueParam)) {
        allGroupList.add(valueParam);
        staticobj.putRecordToObj("All&GroupMember",param,allGroupList);
      }
    }
  }

  @Override
  public CacheMap initCache() {
//    writeLog("KQGroupMemberComInfo::::::in:::::::2222::::::");
    staticobj = StaticObj.getInstance();
    staticobj.removeObject("User&GroupMember");
    staticobj.removeObject("Department&GroupMember");
    staticobj.removeObject("SubCompany&GroupMember");
    staticobj.removeObject("Jobtitle&GroupMember");
    staticobj.removeObject("All&GroupMember");
    getHasValidateGroupInfo();

    CacheMap localData = createCacheMap();
    RecordSet rs = new RecordSet();
    String sql = "";
    kqLog = new KQLog();
    ConcurrentHashMap<String, List> resourceKQGroups = new ConcurrentHashMap<>();
    //考勤组优先级
    try {
      //人员
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE a.id=b.typevalue and b.type =1 and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') order by b.groupid desc ";
      rs.executeQuery(sql);
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }
	  
	  //部门
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b , kq_group c" +
              " WHERE a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3 " +
              " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') order by b.groupid desc";
      rs.executeQuery(sql);
	   while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }

      //分部
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2 " +
              " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') order by b.groupid desc ";
      rs.executeQuery(sql);           
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }

      //岗位
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE a.jobtitle = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=5 " +
              " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
              " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') order by b.groupid desc ";
      rs.executeQuery(sql);
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }

      //所有人
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE (b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto) and b.groupid=c.id " +
              " and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') order by b.groupid desc ";
      rs.executeQuery(sql);
      while (rs.next()) {
        setMemberGroup(resourceKQGroups, rs.getString("resourceid"), rs.getString("groupid"));
      }

      if(resourceKQGroups.size()>0) {
        Iterator<Map.Entry<String, List>> iterator = resourceKQGroups.entrySet().iterator();
        while (iterator.hasNext()) {
          Map.Entry<String, List> entry = iterator.next();
          String id = entry.getKey();
          List value = entry.getValue();
          CacheItem cacheItem = createCacheItem();
          cacheItem.set(PK_INDEX, id);
          cacheItem.set(group, value);
          modifyCacheItem(id, cacheItem);
          localData.put(id, cacheItem);
        }
      }
    } catch (Exception e) {
      kqLog.writeLog(e);
    }
    return localData;
  }

  private void setMemberGroup(ConcurrentHashMap resourceKQGroups, String resourceid, String groupid) {
    try {
      List<Object> kqGroups = null;
      if (resourceKQGroups.get(resourceid) == null) {
        kqGroups = new ArrayList<>();
      } else {
        kqGroups = (List<Object>) resourceKQGroups.get(resourceid);
      }
      kqGroups.add(groupid);
      resourceKQGroups.put(resourceid, kqGroups);
    } catch (Exception e) {
      kqLog.info(e);
    }
  }

  public String getKQGroupId(String userId) {
    String workDate = DateUtil.getCurrentDate();
    return getKQGroupId(userId, workDate);
  }

  /**
   * 根据user获取考勤组id
   *
   * @param userId
   * @return
   */
  public String getKQGroupId(String userId, String workDate) {
    return getKQGroupId(userId, workDate, true);
  }

  /**
   * 是否在考勤组的有效期范围内
   * @param tmpGroupId
   * @param workDate
   * @return
   */
  private boolean isInKqGroupValidate(String tmpGroupId, String workDate) {
    boolean flag = true;
    KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
    String kqtype = kqGroupComInfo.getKqtype(tmpGroupId);
    String validity = kqGroupComInfo.getValidity(tmpGroupId);
    String validityfromdate = kqGroupComInfo.getValidityfromdate(tmpGroupId);
    String validityenddate = kqGroupComInfo.getValidityenddate(tmpGroupId);
    if (validity.equals("1")) {
      if (!DateUtil.isInDateRange(workDate, validityfromdate, validityenddate)) {
        flag = false;
      }
    }
    return flag;
  }
  /***
   * 根据user获取考勤组id
   * @param userId
   * @param workDate
   * @param containFixed 取不到排班取固定班
   * @return
   */
  public String getKQGroupId(String userId, String workDate, boolean containFixed) {
    String groupId = "";
    Map<String, Map<String, String>> allKqGroupsExceptSql = new HashMap<>();
    List<String> kqGroups = getGroupBefore(userId, workDate, allKqGroupsExceptSql);
    if(kqGroups.size() > 0) {
      String tmpGroupId = kqGroups.get(0);
      if(isInKqGroupValidate(tmpGroupId, workDate)) {
        return tmpGroupId;
      }
    }
    KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
    List<String> lsGroupIds = this.getKQGroups(userId, workDate);
    lsGroupIds.removeIf(allKqGroupsExceptSql::containsKey);
    for (int i = 0; i < lsGroupIds.size(); i++) {
      String tmpGroupId = Util.null2String((String) lsGroupIds.get(i));
      if(isInKqGroupValidate(tmpGroupId, workDate)) {
        groupId = tmpGroupId;
      } else {
        continue;
      }
      String kqtype = kqGroupComInfo.getKqtype(tmpGroupId);
//      String validity = kqGroupComInfo.getValidity(tmpGroupId);
//      String validityfromdate = kqGroupComInfo.getValidityfromdate(tmpGroupId);
//      String validityenddate = kqGroupComInfo.getValidityenddate(tmpGroupId);
//      if (validity.equals("1")) {
//        if (DateUtil.isInDateRange(workDate, validityfromdate, validityenddate)) {
//          groupId = tmpGroupId;
//        }else{
//          //超过有效期
//          continue;
//        }
//      } else {
//        groupId = tmpGroupId;
//      }

      //考勤组如果排班为空，取下一个考勤组
      if (groupId.length() > 0) {
        if (containFixed && kqtype.equals("2")) {
          KQShiftScheduleComInfo kqShiftScheduleComInfo = new KQShiftScheduleComInfo();
		  kqShiftScheduleComInfo.setShiftGroupId(Util.getIntValue(groupId));
          if (Util.null2String(kqShiftScheduleComInfo.getSerialId(userId, workDate)).length() > 0 &&
                  Util.null2String(kqShiftScheduleComInfo.getGroupId(userId, workDate)).equals(groupId)) {
            break;
          }
        } else {
          break;
        }
      }
    }
    return groupId;
  }

  public List<String> getKQGroups(String userId, String kqDate) {
//    if(!this.isFormat){
//      return (List<String>) getObjValue(group, userId);
//    }
    RecordSet rs = new RecordSet();
    String sql = "";
    kqLog = new KQLog();
    List<String> kqGroups = new ArrayList<>();
//    Map<String, Map<String, String>> allKqGroupsExceptSql = new HashMap<>();
    //考勤组优先级
    try {
//      Map<String, Map<String, String>> sqlWhereMap = sqlWhereMap();
      ResourceComInfo resourceComInfo = new ResourceComInfo();
//      kqGroups = getGroupBefore(userId, kqDate, allKqGroupsExceptSql);
//      if(kqGroups.size() > 0) {
//        return kqGroups;
//      }
      String subcompanyid = Util.null2String(resourceComInfo.getSubCompanyID(userId));//分部
      String departmentid = Util.null2String(resourceComInfo.getDepartmentID(userId));//部门
      String jobtitle = Util.null2String(resourceComInfo.getJobTitle(userId));//岗位
      String sqlWhere = " and ('"+kqDate+"' between b.validatefrom and b.validateto) ";
//      if(rs.getDBType().equalsIgnoreCase("oracle")) {
//        sqlWhere = " and ('"+kqDate+"' between nvl(b.validatefrom,'2000-01-01') and nvl(b.validateto,'2999-12-31')) ";
//      }else if((rs.getDBType()).equalsIgnoreCase("mysql")){
//        sqlWhere = " and ('"+kqDate+"' between ifnull(b.validatefrom,'2000-01-01') and ifnull(b.validateto,'2999-12-31')) ";
//      }else {
//        sqlWhere = " and ('"+kqDate+"' between isnull(b.validatefrom,'2000-01-01') and isnull(b.validateto,'2999-12-31')) ";
//      }

      //人员
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE a.id=b.typevalue and b.type =1 and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1')" +
              " and a.id = ?"+sqlWhere+
              " order by b.groupid desc ";
      rs.executeQuery(sql,userId);
      while (rs.next()) {
        kqGroups.add(rs.getString("groupid"));
      }

      //部门
      if(departmentid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b , kq_group c" +
                " WHERE a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3 " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.departmentid = ? "+
                " and a.id= ? "+sqlWhere+
                " order by b.groupid desc";
        rs.executeQuery(sql,departmentid,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //分部
      if(subcompanyid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
                " WHERE a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2 " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.subcompanyid1 = ? " +
                " and a.id= ? "+sqlWhere+
                " order by b.groupid desc ";
        rs.executeQuery(sql,subcompanyid,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //岗位
      if(jobtitle.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
                " WHERE a.jobtitle = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=5 " +
                " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
                " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
                " and a.jobtitle = ? " +
                " and a.id= ? "+sqlWhere+
                " order by b.groupid desc ";
        rs.executeQuery(sql,jobtitle,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //所有人
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
              " WHERE (b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto) and b.groupid=c.id " +
              " and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
              " and a.id = ? "+sqlWhere+
              " order by b.groupid desc ";
      rs.executeQuery(sql,userId);
      while (rs.next()) {
        kqGroups.add(rs.getString("groupid"));
      }
    } catch (Exception e) {
      kqLog.info(e);
    }
//    kqGroups.removeIf(allKqGroupsExceptSql::containsKey);
    return kqGroups;
  }

  /**
   * 如果设置了有效期，且查询在有效期范围内的，直接用
   * @return
   */
  private List<String> getGroupBefore(String userId, String kqDate, Map<String, Map<String, String>> allKqGroupsExceptSql) {
    StaticObj groupMemberStaticObj = StaticObj.getInstance();
    RecordSet rs = new RecordSet();
    List<String> kqGroups = new ArrayList<>();
    try {
      ResourceComInfo resourceComInfo = new ResourceComInfo();
      String subcompanyid = Util.null2String(resourceComInfo.getSubCompanyID(userId));//分部
      String departmentid = Util.null2String(resourceComInfo.getDepartmentID(userId));//部门
      String jobtitle = Util.null2String(resourceComInfo.getJobTitle(userId));//岗位
      // 人员
      String param = "type#1#"+userId;
      if(groupMemberStaticObj.getObject("User&GroupMember") != null) {
        List<String> userGroupList = (List<String>)groupMemberStaticObj.getRecordFromObj("User&GroupMember",param);
//        writeLog("userGroupList::::"+ JSON.toJSONString(userGroupList));
        if(userGroupList != null) {
          for (String userGourp : userGroupList) {
            String groupId = userGourp.split("#")[0];
            handle(allKqGroupsExceptSql, groupId, userId, "resourceId");
            String validateFrom = userGourp.split("#")[1];
            String validateTo = userGourp.split("#")[2];
            if (kqDate.compareTo(validateFrom) >= 0 && kqDate.compareTo(validateTo) <= 0) {
              kqGroups.add(groupId);
            }
          }
          if (kqGroups.size() > 0) {
            return kqGroups;
          }
        }
      }
      //部门
      if (departmentid.length() > 0) {
        param = "type#3#" + departmentid;
        if (groupMemberStaticObj.getObject("Department&GroupMember") != null) {
          List<String> departGroupList = (List<String>) groupMemberStaticObj.getRecordFromObj("Department&GroupMember", param);
//          writeLog("departGroupList::::" + JSON.toJSONString(departGroupList));
          if (departGroupList != null) {
            for (String departGroup : departGroupList) {
              String groupId = departGroup.split("#")[0];
              String validateFrom = departGroup.split("#")[1];
              String validateTo = departGroup.split("#")[2];
              String groupseclevel = departGroup.split("#")[3];
              String groupseclevelto = departGroup.split("#")[4];
              int userSeclevel = Util.getIntValue(resourceComInfo.getSeclevel(userId));
              if (kqDate.compareTo(validateFrom) >= 0 && kqDate.compareTo(validateTo) <= 0 && userSeclevel>=Util.getIntValue(groupseclevel)
                      && userSeclevel<=Util.getIntValue(groupseclevelto)) {
                if (allKqGroupsExceptSql.containsKey(groupId)) {
                  continue;
                }
                handle(allKqGroupsExceptSql, groupId, departmentid, "departmentId");
                kqGroups.add(groupId);
              }
            }
          }
          if (kqGroups.size() > 0) {
            return kqGroups;
          }
        }
      }

      //分部
      if (subcompanyid.length() > 0) {
        param = "type#2#" + subcompanyid;
        if (groupMemberStaticObj.getObject("SubCompany&GroupMember") != null) {
          List<String> subCompanyGroupList = (List<String>) groupMemberStaticObj.getRecordFromObj("SubCompany&GroupMember", param);
//          writeLog("subCompanyGroupList::::" + JSON.toJSONString(subCompanyGroupList));
          if (subCompanyGroupList != null) {
            for (String subCompanyGroup : subCompanyGroupList) {
              String groupId = subCompanyGroup.split("#")[0];
              String validateFrom = subCompanyGroup.split("#")[1];
              String validateTo = subCompanyGroup.split("#")[2];
              String groupseclevel = subCompanyGroup.split("#")[3];
              String groupseclevelto = subCompanyGroup.split("#")[4];
              int userSeclevel = Util.getIntValue(resourceComInfo.getSeclevel(userId));
              if (kqDate.compareTo(validateFrom) >= 0 && kqDate.compareTo(validateTo) <= 0 && userSeclevel>=Util.getIntValue(groupseclevel)
                      && userSeclevel<=Util.getIntValue(groupseclevelto)) {
                if (allKqGroupsExceptSql.containsKey(groupId)) {
                  continue;
                }
                handle(allKqGroupsExceptSql, groupId, subcompanyid, "subcompanyId");
                kqGroups.add(groupId);
              }
            }
          }
          if (kqGroups.size() > 0) {
            return kqGroups;
          }
        }
      }

      //岗位
      if (jobtitle.length() > 0) {
        param = "type#5#" + jobtitle;
        if (groupMemberStaticObj.getObject("Jobtitle&GroupMember") != null) {
          List<String> jobtitleGroupList = (List<String>) groupMemberStaticObj.getRecordFromObj("Jobtitle&GroupMember", param);
//          writeLog("jobtitleGroupList::::" + JSON.toJSONString(jobtitleGroupList));
          if (jobtitleGroupList != null) {
            for (String jobtitleGroup : jobtitleGroupList) {
              String groupId = jobtitleGroup.split("#")[0];
              String validateFrom = jobtitleGroup.split("#")[1];
              String validateTo = jobtitleGroup.split("#")[2];
               String groupseclevel = jobtitleGroup.split("#")[3];
              String groupseclevelto = jobtitleGroup.split("#")[4];
              String jobtitlelevel = jobtitleGroup.split("#")[5];
              String jobtitlelevelvalue = jobtitleGroup.split("#")[6];
              int userSeclevel = Util.getIntValue(resourceComInfo.getSeclevel(userId));
              if("2".equals(jobtitlelevel)) {  // 分部
                String subcompanyId = ","+resourceComInfo.getSubCompanyID(userId)+",";
                if(!jobtitlelevelvalue.contains(subcompanyId)) {
                  continue;
                }
              } else if("3".equals(jobtitlelevel)) {  // 部门
                String departmentId = ","+resourceComInfo.getDepartmentID(userId)+",";
                if(!jobtitlelevelvalue.contains(departmentId)) {
                  continue;
                }
              }
              if (kqDate.compareTo(validateFrom) >= 0 && kqDate.compareTo(validateTo) <= 0 && userSeclevel>=Util.getIntValue(groupseclevel)
                      && userSeclevel<=Util.getIntValue(groupseclevelto)) {
                if (allKqGroupsExceptSql.containsKey(groupId)) {
                  continue;
                }
                handle(allKqGroupsExceptSql, groupId, jobtitle, "jobtitleId");
                kqGroups.add(groupId);
              }
            }
          }
          if (kqGroups.size() > 0) {
            return kqGroups;
          }
        }
      }

      //所有人
      param = "type#6#0";
      if (groupMemberStaticObj.getObject("All&GroupMember") != null) {
        List<String> allGroupList = (List<String>) groupMemberStaticObj.getRecordFromObj("All&GroupMember", param);
//        writeLog("allGroupList::::" + JSON.toJSONString(allGroupList));
        if (allGroupList != null) {
          for (String allGroup : allGroupList) {
            String groupId = allGroup.split("#")[0];
            String validateFrom = allGroup.split("#")[1];
            String validateTo = allGroup.split("#")[2];
            String groupseclevel = allGroup.split("#")[3];
            String groupseclevelto = allGroup.split("#")[4];
            int userSeclevel = Util.getIntValue(resourceComInfo.getSeclevel(userId));
            if (kqDate.compareTo(validateFrom) >= 0 && kqDate.compareTo(validateTo) <= 0 && userSeclevel>=Util.getIntValue(groupseclevel)
                    && userSeclevel<=Util.getIntValue(groupseclevelto)) {
              if (allKqGroupsExceptSql.containsKey(groupId)) {
                continue;
              }
              handle(allKqGroupsExceptSql, groupId, "0", "all");
              kqGroups.add(groupId);
            }
          }
          if (kqGroups.size() > 0) {
            return kqGroups;
          }
        }
      }
    }catch (Exception e) {
      writeLog(e);
    }
    return kqGroups;
  }

  private Map<String, Map<String, String>> sqlWhereMap() {
    // 先把所有的groupid找到
    RecordSet rs = new RecordSet();
    Map<String, Map<String, String>> allKqGroupsExceptSql = new HashMap<>();
    String sql = "select * from kq_groupmember where (isDelete is null or isDelete<>1)";
    rs.executeQuery(sql);
    while (rs.next()) {
      String groupId = rs.getString("id");
      int groupType = rs.getInt("type");
      String groupTypeValue = rs.getString("typevalue");

      switch (groupType) {
        case 1:
          handle(allKqGroupsExceptSql, groupId, groupTypeValue, "resourceId");
          break;
        case 2:
          handle(allKqGroupsExceptSql, groupId, groupTypeValue, "subcompanyId");
          break;
        case 3:
          handle(allKqGroupsExceptSql, groupId, groupTypeValue, "departmentId");
          break;
        case 5:
          handle(allKqGroupsExceptSql, groupId, groupTypeValue, "jobTitleId");
          break;
        case 6:
          handle(allKqGroupsExceptSql, groupId, groupTypeValue, "all");
          break;
        default:
          break;
      }

    }
    return allKqGroupsExceptSql;
  }

  private void handle(Map<String, Map<String, String>> allKqGroupsExceptSql, String groupId, String groupTypeValue, String key) {
    Map<String, String> sqlWhereMap = null;
    String sqlWhereValue = groupTypeValue;
    if(allKqGroupsExceptSql.containsKey(groupId)) {
      sqlWhereMap = allKqGroupsExceptSql.get(groupId);
      if (sqlWhereMap.containsKey(key)) {
        sqlWhereValue = sqlWhereMap.get(key) + "," + groupTypeValue;
      }
    } else {
      sqlWhereMap = new HashMap<>();
    }
    sqlWhereMap.put(key, sqlWhereValue);
    allKqGroupsExceptSql.put(groupId, sqlWhereMap);
  }
  /**
   * 根据user获取所有考勤组
   *
   * @param userId
   * @return
   */
  private List<String> getKQGroups(String userId) {
    if(!this.isFormat){
      return (List<String>) getObjValue(group, userId);
    }
    RecordSet rs = new RecordSet();
    String sql = "";
    kqLog = new KQLog();
    List<String> kqGroups = new ArrayList<>();
    //考勤组优先级
    try {
      ResourceComInfo resourceComInfo = new ResourceComInfo();

      String subcompanyid = Util.null2String(resourceComInfo.getSubCompanyID(userId));//分部
      String departmentid = Util.null2String(resourceComInfo.getDepartmentID(userId));//部门
      String jobtitle = Util.null2String(resourceComInfo.getJobTitle(userId));//岗位

      //人员
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
        " WHERE a.id=b.typevalue and b.type =1 and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1')" +
        " and a.id = ?"+
        " order by b.groupid desc ";
      rs.executeQuery(sql,userId);
      while (rs.next()) {
        kqGroups.add(rs.getString("groupid"));
      }

      //部门
      if(departmentid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b , kq_group c" +
          " WHERE a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3 " +
          " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
          " and a.departmentid = ? "+
          " and a.id= ? "+
          " order by b.groupid desc";
        rs.executeQuery(sql,departmentid,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //分部
      if(subcompanyid.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
          " WHERE a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2 " +
          " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
          " and a.subcompanyid1 = ? " +
          " and a.id= ? "+
          " order by b.groupid desc ";
        rs.executeQuery(sql,subcompanyid,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //岗位
      if(jobtitle.length()>0) {
        sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
          " WHERE a.jobtitle = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=5 " +
          " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
          " and b.groupid=c.id and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
          " and a.jobtitle = ? " +
          " and a.id= ? "+
          " order by b.groupid desc ";
        rs.executeQuery(sql,jobtitle,userId);
        while (rs.next()) {
          kqGroups.add(rs.getString("groupid"));
        }
      }

      //所有人
      sql = " SELECT distinct a.id AS resourceid, b.groupid, a.status FROM HrmResource a, kq_groupmember b, kq_group c " +
        " WHERE (b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto) and b.groupid=c.id " +
        " and (c.isdelete is null or c.isdelete <> '1') and (b.isdelete is null or b.isdelete <> '1') " +
        " and a.id = ? "+
        " order by b.groupid desc ";
      rs.executeQuery(sql,userId);
      while (rs.next()) {
        kqGroups.add(rs.getString("groupid"));
      }
    } catch (Exception e) {
      kqLog.info(e);
    }
    return kqGroups;
  }

  /**
   * 根据user获取所有考勤组id
   *
   * @param userId
   * @return
   */
  public String getKQGroupIds(String userId) {
    String groupId = "";
    String kqDate = DateUtil.getCurrentDate();
    List<String> lsGroupIds = this.getKQGroups(userId, kqDate);
    if (lsGroupIds != null) {
      groupId = String.join(",", lsGroupIds);
    }
    return groupId;
  }

  public String getKQGroupIds(String userId, String kqDate) {
    String groupId = "";
    List<String> lsGroupIds = this.getKQGroups(userId, kqDate);
    if (lsGroupIds != null) {
      groupId = String.join(",", lsGroupIds);
    }
    return groupId;
  }


  public KQGroupEntity getUserKQGroupInfo(String userId) {
    String workDate = DateUtil.getCurrentDate();
    return getUserKQGroupInfo(userId, workDate);
  }

  /**
   * 获取考勤组相关信息
   *
   * @param userId
   * @return
   */
  public KQGroupEntity getUserKQGroupInfo(String userId, String workDate) {
    return getUserKQGroupInfo(userId, workDate, true);
  }

  /***
   * 获取考勤组相关信息
   * @param userId
   * @param workDate
   * @param containFixed 取不到排班取固定班
   * @return
   */
  public KQGroupEntity getUserKQGroupInfo(String userId, String workDate, boolean containFixed) {
    KQGroupEntity kqGroupEntity = null;
    KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
    try {
      String groupid = Util.null2String(this.getKQGroupId(userId, workDate, containFixed));
      if (groupid.length() > 0) {
        kqGroupEntity = new KQGroupEntity();
        kqGroupEntity.setId(groupid);
        kqGroupEntity.setGroupname(kqGroupComInfo.getGroupname(groupid));
        kqGroupEntity.setExcludeid(kqGroupComInfo.getExcludeid(groupid));
        kqGroupEntity.setSubcompanyid(kqGroupComInfo.getSubcompanyid(groupid));
        kqGroupEntity.setKqtype(kqGroupComInfo.getKqtype(groupid));
        kqGroupEntity.setSerialids(kqGroupComInfo.getSerialids(groupid));
        kqGroupEntity.setWeekday(kqGroupComInfo.getWeekday(groupid));
        kqGroupEntity.setSignstart(kqGroupComInfo.getSignstart(groupid));
        kqGroupEntity.setWorkhour(kqGroupComInfo.getWorkhour(groupid));
        kqGroupEntity.setIsdelete(kqGroupComInfo.getIsdelete(groupid));
        kqGroupEntity.setSignintype(kqGroupComInfo.getSignintype(groupid));
        kqGroupEntity.setIpscope(kqGroupComInfo.getIpscope(groupid));
        kqGroupEntity.setLocationcheck(kqGroupComInfo.getLocationcheck(groupid));
        kqGroupEntity.setLocationcheckscope(kqGroupComInfo.getLocationcheckscope(groupid));
        kqGroupEntity.setWificheck(kqGroupComInfo.getWificheck(groupid));
        kqGroupEntity.setOutsidesign(kqGroupComInfo.getOutsidesign(groupid));
        kqGroupEntity.setOutsignapprove(kqGroupComInfo.getOutsignApprove(groupid));
        kqGroupEntity.setCalmethod(kqGroupComInfo.getCalmethod(groupid));
        kqGroupEntity.setLocationshowaddress(kqGroupComInfo.getLocationshowaddress(groupid));
		kqGroupEntity.setAbnormalremind(kqGroupComInfo.getAbnormalremind(groupid));
      }
    } catch (Exception e) {
      kqLog.info(e);
    }
    return kqGroupEntity;
  }

  public void removeCache() {
    super.removeCache();

  }

  public void setIsFormat(boolean isFormat){
    this.isFormat = isFormat;
  }
}